package 期中22;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

import 期中22.Pd;

public class Thesql {
	public Connection connect;
	public Thesql()throws Exception 
	{
		Class.forName("com.mysql.jdbc.Driver");
		String url="jdbc:mysql://localhost:3306/qizhonglianxi";
		String username="root";
		String password="2223640185";
		connect = DriverManager.getConnection(url,username,password);
	}
	public void finalize() throws Exception
	{
		connect.close();
	}
	public void add(String zhuti,String mudi,int leixing,String shijian,String didian,int duixiang1,int duixiang2,String neirong,String anpai)throws Exception 
	{
		String sql = "insert into qizhong22(zhuti,mudi,leixing,shijian,didian,duixiang1,duixiang2,neirong,anpai) values(?,?,?,?,?,?,?,?,?);";
		PreparedStatement pre = connect.prepareStatement(sql);
		pre.setString(1,zhuti);
		pre.setString(2,mudi);
		pre.setInt(3,leixing);
		pre.setString(4,shijian);
		pre.setString(5,didian);
		pre.setInt(6,duixiang1);
		pre.setInt(7,duixiang2);
		pre.setString(8,neirong);
		pre.setString(9,anpai);
		int count=pre.executeUpdate();
		pre.close();
	}
	public void xiugai(int id,String mudi,int leixing,String shijian,String didian,int duixiang1,int duixiang2,String neirong,String anpai)throws Exception 
	{
		String sql = "update qizhong22 set mudi=?,leixing=?,shijian=?,didian=?,duixiang1=?,duixiang2=?,neirong=?,anpai=? where id=?";
		PreparedStatement pre = connect.prepareStatement(sql);
		pre.setString(1,mudi);
		pre.setInt(2,leixing);
		pre.setString(3,shijian);
		pre.setString(4,didian);
		pre.setInt(5,duixiang1);
		pre.setInt(6,duixiang2);
		pre.setString(7,neirong);
		pre.setString(8,anpai);
		pre.setInt(9,id);
		int count=pre.executeUpdate();
		pre.close();
	}
	public void del(int id) throws Exception
	{
		String sql="delete from qizhong22 where id = ?";
		PreparedStatement pre = connect.prepareStatement(sql);
		pre.setInt(1,id);
		int count=pre.executeUpdate();
		pre.close();
	}
	/*精准查询，非模糊*/
	public int[] chaxun(String zhuti,String shijian,String didian,int leixing) throws Exception
	{
		
		String sql="select * from qizhong22;";
		Statement stmt = connect.createStatement();
		ResultSet rs = stmt.executeQuery(sql);
		int[] arr=new int[10];
		int i=0;
		while(rs.next())
		{
			int id=rs.getInt(1);
			String tzhuti=rs.getString(2);
			String tshijian=rs.getString(5);
			String tdidian=rs.getString(6);
			int tleixing=rs.getInt(4);
			if((tzhuti.equals(zhuti)&&zhuti!=null)||zhuti==null||zhuti.equals(""))
				if((tshijian.equals(shijian)&&shijian!=null)||shijian==null||shijian.equals(""))
					if((tdidian.equals(zhuti)&&didian!=null)||didian==null||didian.equals(""))
						if((leixing==tleixing&&leixing!=-1)||leixing==-1)
							{
								arr[i]=id;
								i++;
							}
		}
		stmt.close();
		return arr;
	}
	/*模糊查询*/
	public int[] chaxun1(String zhuti,String shijian,String didian,int leixing) throws Exception
	{
		String sql;
		if(leixing==-1)
			 sql="select * from qizhong22 where zhuti like '%"+zhuti+"%' and shijian like '%"+shijian+"%' and didian like '%"+didian+"%'";
		else sql="select * from qizhong22 where zhuti like '%"+zhuti+"%' and shijian like '%"+shijian+"%' and didian like '%"+didian+"%' and leixing like '"+leixing+"'";
		Statement stmt = connect.createStatement();
		ResultSet rs = stmt.executeQuery(sql);
		int[] arr=new int[10];
		int i=0;
		while(rs.next())
		{
			int id=rs.getInt(1);
			arr[i]=id;
			i++;
		}
		stmt.close();
		return arr;
	}
	
	
	
	
	
	
	
	
	
	public boolean pdname(String str) throws Exception
	{
		String sql="select * from qizhong22;";
		Statement stmt = connect.createStatement();
		ResultSet rs = stmt.executeQuery(sql);
		while(rs.next())
		{
			String zhuti=rs.getString(2);
			if(zhuti.equals(str))
				return true;
		}
		return false;
	}
	public int findname(String str) throws Exception
	{
		String sql="select * from qizhong22;";
		Statement stmt = connect.createStatement();
		ResultSet rs = stmt.executeQuery(sql);
		while(rs.next())
		{
			String zhuti=rs.getString(2);
			if(zhuti.equals(str))
				return rs.getInt(1);
		}
		return -1;
	}
	public Pd show(int tid) throws Exception
	{
		String sql="select * from qizhong22 where id ="+tid;
		Statement stmt = connect.createStatement();
		ResultSet rs = stmt.executeQuery(sql);
		Pd ppd=new Pd();
		while(rs.next())
		{
			ppd.id=rs.getInt(1);
			ppd.zhuti=rs.getString(2);
			ppd.mudi=rs.getString(3);
			ppd.leixing=rs.getInt(4);
			ppd.shijian=rs.getString(5);
			ppd.didian=rs.getString(6);
			ppd.duixiang1=rs.getInt(7);
			ppd.duixiang2=rs.getInt(8);
			ppd.neirong=rs.getString(9);
			ppd.anpai=rs.getString(10);
		}
		stmt.close();
		return ppd;
	}
	
	
	
	
	
	
	
	
}
